package com.allen.service;

import com.allen.Utils.JdbcUtils;
import com.allen.bean.User;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserService {

    private Connection connection;
    private Statement statement;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    public int valid(String name) {
        connection = JdbcUtils.getConnect();
        String sql = "select * from user where username= '" + name + "'";
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                return 1;
            } else {
                return 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return -1;
    }

    public User login(String username, String password) {
        connection = JdbcUtils.getConnect();
        String sql = "SELECT * FROM user WHERE username='" + username + "' and password='" + password + "'";
        User user = new User();
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setNickName(resultSet.getString("nickName"));
                user.setIsAdmin(resultSet.getInt("isAdmin"));
                user.setState(resultSet.getInt("state"));
                user.setDeleted(resultSet.getInt("deleted"));
                return user;
            } else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public int alterPwd(String pwd, int id) {
        connection = JdbcUtils.getConnect();
        String sql = "UPDATE user SET password='" + pwd + "' WHERE id='" + id + "'";
        int i = 0;
        try {
            statement = connection.createStatement();
            i = statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }

    public List<User> getAllUser() {
        List<User> userList = new ArrayList<>();
        String sql = "SELECT * FROM user;";

        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setNickName(resultSet.getString("nickName"));
                user.setIsAdmin(resultSet.getInt("isAdmin"));
                user.setState(resultSet.getInt("state"));
                user.setDeleted(resultSet.getInt("deleted"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return userList;
    }

    public void addUser(HttpServletRequest req, HttpServletResponse resp){
        String username = req.getParameter("username");
        String nickName = req.getParameter("nickName");
        Integer isAdmin = 0;
        Integer state = 0;
        Integer deleted = 0;
        if (req.getParameter("isAdmin") != null) {
            isAdmin = 1;
        }
        if (req.getParameter("state") != null) {
            state = 1;
        }
        if (req.getParameter("deleted") != null) {
            deleted = 1;
        }
        String sql = "insert into user (username,password,nickName,isAdmin,state,deleted) values(?,?,?,?,?,?)";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, "123");
            preparedStatement.setString(3, nickName);
            preparedStatement.setInt(4, isAdmin);
            preparedStatement.setInt(5, state);
            preparedStatement.setInt(6, deleted);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public User findById(int id) {
        User user = new User();
        connection = JdbcUtils.getConnect();
        String sql = "select * from user where id=" + id;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                user.setId(id);
                user.setUsername(resultSet.getString("username"));
                user.setNickName(resultSet.getString("nickName"));
                user.setIsAdmin(resultSet.getInt("isAdmin"));
                user.setState(resultSet.getInt("state"));
                user.setDeleted(resultSet.getInt("deleted"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }

    public void updateUser(HttpServletRequest req, HttpServletResponse resp) {
        Integer id = Integer.parseInt(req.getParameter("id"));
        String username = req.getParameter("username");
        String nickName = req.getParameter("nickName");
        Integer isAdmin = 0;
        Integer state = 0;
        Integer deleted = 0;
        if (req.getParameter("isAdmin") != null) {
            isAdmin = 1;
        }
        if (req.getParameter("state") != null) {
            state = 1;
        }
        if (req.getParameter("deleted") != null) {
            deleted = 1;
        }
        String sql = "UPDATE user SET username=?,nickName=?,isAdmin=?,state=?,deleted=? WHERE id=?";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, nickName);
            preparedStatement.setInt(3, isAdmin);
            preparedStatement.setInt(4, state);
            preparedStatement.setInt(5, deleted);
            preparedStatement.setInt(6,id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void deleteUser(Integer id) {
        String sql = "DELETE FROM user WHERE id=?";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
